package com.iMusic.businessLogicLayer;

import java.io.IOException;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.iMusic.integrationLayer.DBConnection;
import com.iMusic.integrationLayer.DBConnectionManager;
import com.iMusic.util.UserMessages;

import java.lang.Integer;

public class Account {

	private int accountId = 0;
	private String name = null;
	private String sex = null;
	private Date birthday = null;
	private String username = null;
	private String passwd = null;
	private String email = null;
	private Date dateCreated = null;
	private Date dateModified = null;
	private String vip = null;
	private String financialInstitution = null;
	private String creditDirect = null;
	private String accountNumber = null;
	private Date expiryDate = null;
	private DBConnection dbConn = null;
	private Cart cart = null;
	private Address address = null;
	
	static private DBConnectionManager dbConnMgr = null;

	/*
	 * Constructor
	 */
	public Account() {
		if (dbConnMgr == null) {
			dbConnMgr = DBConnectionManager.getDBConnectionManager();
		}
		
	}

	
	/*
	 * Get Account
	 */
	static public Account getAccount(long l) throws SQLException {
		Account acc = new Account();
		
		DBConnection dbConn = DBConnectionManager.getDBConnectionManager().getDBConnection();
		
		dbConn.connect();
		
		ResultSet rs = dbConn.query("SELECT * FROM ACCOUNT WHERE AccountId = " + l);
		
		if (rs != null && rs.next()) {
			
			acc.setAccountId(rs.getInt("AccountId"));
			acc.setName(rs.getString("Name"));
			acc.setUsername(rs.getString("Username"));
			acc.setSex(rs.getString("Sex"));
			acc.setBirthday(rs.getDate("Birthday"));
			acc.setEmail(rs.getString("Email"));
			acc.setDateCreated(rs.getDate("DateCreated"));
			acc.setDateModified(rs.getDate("DateModified"));
			acc.setVip(rs.getString("VIP"));
			acc.setFinancialInstitution(rs.getString("FinancialInstitution"));
			acc.setCreditDirect(rs.getString("CreditDirect"));
			acc.setAccountNumber(rs.getString("AccountNumber"));
			acc.setExpiryDate(rs.getDate("ExpiryDate"));

			rs.close();
			rs = null;

			
			rs = dbConn.query("SELECT AddressId, Street, StreetNumber, Complement, PostalCode, City, Province, Country " +
								"FROM ADDRESS " +
								"WHERE AccountId = " + acc.getAccountId() + " ");

			if (rs != null && rs.next()) {
			
				Address address = new Address();
				address.setAddressId(Long.parseLong(rs.getString("AddressId")));
				address.setStreet(rs.getString("Street"));
				address.setStreetNbr(rs.getString("StreetNumber"));
				address.setComplement(rs.getString("Complement"));
				address.setPostalCode(rs.getString("PostalCode"));
				address.setCity(rs.getString("City"));
				address.setProvince(rs.getString("Province"));
				address.setCountry(rs.getString("Country"));
				
			
				rs.close();
				rs = null;

				acc.setAddress(address);
			
			}
		}

		dbConn.disconnect();
		dbConnMgr.freeDBConnection(dbConn);

		return acc;
	}
	
	
	/*
	 * Login into Account
	 */
	public boolean login() throws SQLException {
		
		boolean ret = false;
		
		if (dbConn != null) {
			dbConnMgr.freeDBConnection(dbConn);
		}
		
		dbConn = dbConnMgr.getDBConnection();
		dbConn.connect();
		
		ResultSet rs = dbConn.query("SELECT * FROM ACCOUNT WHERE USERNAME = '" + username + "'" +
										"AND PASSWORD = password('" + passwd + "')");
		
		if (rs != null && rs.next()) {
			
			setAccountId(rs.getInt("AccountId"));
			setName(rs.getString("Name"));
			setSex(rs.getString("Sex"));
			setBirthday(rs.getDate("Birthday"));
			setEmail(rs.getString("Email"));
			setDateCreated(rs.getDate("DateCreated"));
			setDateModified(rs.getDate("DateModified"));
			setVip(rs.getString("VIP"));
			setFinancialInstitution(rs.getString("FinancialInstitution"));
			setCreditDirect(rs.getString("CreditDirect"));
			setAccountNumber(rs.getString("AccountNumber"));
			setExpiryDate(rs.getDate("ExpiryDate"));

			rs.close();
			rs = null;

			
			rs = dbConn.query("SELECT AddressId, Street, StreetNumber, Complement, PostalCode, City, Province, Country " +
								"FROM ADDRESS " +
								"WHERE AccountId = " + getAccountId() + " ");

			if (rs != null && rs.next()) {
			
				Address address = new Address();
				address.setAddressId(Long.parseLong(rs.getString("AddressId")));
				address.setStreet(rs.getString("Street"));
				address.setStreetNbr(rs.getString("StreetNumber"));
				address.setComplement(rs.getString("Complement"));
				address.setPostalCode(rs.getString("PostalCode"));
				address.setCity(rs.getString("City"));
				address.setProvince(rs.getString("Province"));
				address.setCountry(rs.getString("Country"));
				
			
				rs.close();
				rs = null;

				setAddress(address);
			
				ret = true;
			}
		}
		
		return ret;
	}

	
	/*
	 * Logout
	 */
	public boolean logout() {
		boolean ret = false;
		
		dbConn.disconnect();
		if (dbConnMgr.freeDBConnection(dbConn)) {
			ret = true;
		}
		
		System.out.println("User " + username + " logged out.");

		
		return ret;
	}
	
	
	/*
	 * Creates new account
	 */
	public boolean createAccount() throws SQLException {
		boolean ret = false;
		
		if (dbConn != null) {
			dbConnMgr.freeDBConnection(dbConn);
		}
		
		dbConn = dbConnMgr.getDBConnection();
		dbConn.connect();
		
		Integer nextAccountId = 1;
		ResultSet rs = dbConn.query("SELECT MAX(AccountId)+1 FROM ACCOUNT");
		if (rs.next()) {
			nextAccountId = rs.getInt(1);
		}
		
		String sqlStmt = "INSERT INTO ACCOUNT (AccountId, Name, Sex, Birthday, Username, Password, " +
				"Email, DateCreated, DateModified, VIP, FinancialInstitution, CreditDirect, " + 
				"AccountNumber, ExpiryDate) VALUES (" + nextAccountId + ", '" + name + "', '" + sex + "', " +
				(birthday==null ? "null" : "'"+birthday+"'") + ", '" + username + "', password('" + passwd + "'), '" + email + "', curdate(), curdate(), '" +
				vip + "', '" + financialInstitution + "', '" + creditDirect + "', '" +
				accountNumber + "', " + (expiryDate==null ? "null" : "'"+expiryDate+"'") + ")";
		System.out.println(sqlStmt);
		dbConn.execSQL(sqlStmt);
		dbConn.execSQL("commit");
		

		sqlStmt = "INSERT INTO ADDRESS (AccountId, AddressId, Street, StreetNumber, Complement, PostalCode, City, Province, Country) " +
					"VALUES (" + nextAccountId + ", 1, '" + address.getStreet() + "', '" + address.getStreetNbr() + "', '" +
				address.getComplement() + "', '" + address.getPostalCode() + "', '" + address.getCity() + "', '" + address.getProvince() + "', '" +
					address.getCountry() + "')";
		System.out.println(sqlStmt);
		dbConn.execSQL(sqlStmt);
		dbConn.execSQL("commit");
		
		dbConn.disconnect();
		
		ret = true;
		
		return ret;
	}
	
	
	/*
	 * Returns the DBConnection
	 */
	public DBConnection getDBConnection() {
		return dbConn;
	}
	
	/**
	 * @return the username
	 */
	public String getUsername() {
		return username;
	}


	/**
	 * @param username the username to set
	 */
	public void setUsername(String username) {
		this.username = username;
	}


	/**
	 * @return the passwd
	 */
	public String getPasswd() {
		return passwd;
	}


	/**
	 * @param passwd the passwd to set
	 */
	public void setPasswd(String passwd) {
		this.passwd = passwd;
	}


	/**
	 * @return the accountId
	 */
	public int getAccountId() {
		return accountId;
	}


	/**
	 * @param accountId the accountId to set
	 */
	public void setAccountId(int accountId) {
		this.accountId = accountId;
	}


	/**
	 * @return the name
	 */
	public String getName() {
		return name;
	}


	/**
	 * @param name the name to set
	 */
	public void setName(String name) {
		this.name = name;
	}


	/**
	 * @return the sex
	 */
	public String getSex() {
		return sex;
	}


	/**
	 * @param sex the sex to set
	 */
	public void setSex(String sex) {
		this.sex = sex;
	}


	/**
	 * @return the birthday
	 */
	public Date getBirthday() {
		return birthday;
	}


	/**
	 * @param birthday the birthday to set
	 */
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}


	/**
	 * @return the email
	 */
	public String getEmail() {
		return email;
	}


	/**
	 * @param email the email to set
	 */
	public void setEmail(String email) {
		this.email = email;
	}


	/**
	 * @return the dateCreated
	 */
	public Date getDateCreated() {
		return dateCreated;
	}


	/**
	 * @param dateCreated the dateCreated to set
	 */
	public void setDateCreated(Date dateCreated) {
		this.dateCreated = dateCreated;
	}


	/**
	 * @return the dateModified
	 */
	public Date getDateModified() {
		return dateModified;
	}


	/**
	 * @param dateModified the dateModified to set
	 */
	public void setDateModified(Date dateModified) {
		this.dateModified = dateModified;
	}


	/**
	 * @return the vip
	 */
	public String getVip() {
		return vip;
	}


	/**
	 * @param vip the vip to set
	 */
	public void setVip(String vip) {
		this.vip = vip;
	}


	/**
	 * @return the financialInstitution
	 */
	public String getFinancialInstitution() {
		return financialInstitution;
	}


	/**
	 * @param financialInstitution the financialInstitution to set
	 */
	public void setFinancialInstitution(String financialInstitution) {
		this.financialInstitution = financialInstitution;
	}


	/**
	 * @return the creditDirect
	 */
	public String getCreditDirect() {
		return creditDirect;
	}


	/**
	 * @param creditDirect the creditDirect to set
	 */
	public void setCreditDirect(String creditDirect) {
		this.creditDirect = creditDirect;
	}


	/**
	 * @return the accountNumber
	 */
	public String getAccountNumber() {
		return accountNumber;
	}


	/**
	 * @param accountNumber the accountNumber to set
	 */
	public void setAccountNumber(String accountNumber) {
		this.accountNumber = accountNumber;
	}


	/**
	 * @return the expiryDate
	 */
	public Date getExpiryDate() {
		return expiryDate;
	}


	/**
	 * @param expiryDate the expiryDate to set
	 */
	public void setExpiryDate(Date expiryDate) {
		this.expiryDate = expiryDate;
	}


	/**
	 * @return the cart
	 */
	public Cart getCart() {
		return cart;
	}


	/**
	 * @param cart the cart to set
	 */
	public void setCart(Cart cart) {
		this.cart = cart;
	}


	public Address getAddress() {
		return address;
	}


	public void setAddress(Address address) {
		this.address = address;
	}
	
	
}
